﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace org.maquech.CEeL.Core.People {
    public class Person : PersistentObject {
        #region Fields and Properties

        /// <summary>
        /// The Person's name field.
        /// </summary>
        protected string name;

        /// <summary>
        /// The Person's name.
        /// </summary>
        public string Name {
            get {
                return this.name;
            }
            set {
                if ( this.name != value )
                    modifiedDBFields.Add( "name" );
                this.name = value;
            }
        }

        /// <summary>
        /// The Person's name middle name field.
        /// </summary>
        protected string middleName;

        /// <summary>
        /// The Person's Middle Name.
        /// </summary>
        public string MiddleName {
            get {
                return this.middleName;
            }
            set {
                if ( this.middleName != value )
                    modifiedDBFields.Add( "middle_name" );
                this.middleName = value;
            }
        }

        /// <summary>
        /// The Person's patronymic field.
        /// </summary>
        protected string patronymic;

        /// <summary>
        /// The Person's patronymic.
        /// </summary>
        public string Patronymic {
            get {
                return this.patronymic;
            }
            set {
                if ( this.patronymic != value )
                    modifiedDBFields.Add( "patronymic" );
                this.patronymic = value;
            }
        }

        /// <summary>
        /// The Person's matronymic field.
        /// </summary>
        protected string matronymic;

        /// <summary>
        /// The Person's Matronymic value.
        /// </summary>
        public string Matronymic {
            get {
                return this.matronymic;
            }
            set {
                if ( this.matronymic != value )
                    modifiedDBFields.Add( "matronymic" );
                this.matronymic = value;
            }
        }

        /// <summary>
        /// The Person's date of birth field.
        /// </summary>
        protected DateTime dateOfBirth;

        /// <summary>
        /// Date Of Birth
        /// </summary>
        public DateTime DateOfBirth {
            get {
                return this.dateOfBirth;
            }
            set {
                if ( this.dateOfBirth != value )
                    modifiedDBFields.Add( "dob" );
                this.dateOfBirth = value;
            }
        }

        /// <summary>
        /// The Person's creation field.
        /// </summary>
        protected DateTime createdAt;

        /// <summary>
        /// The person's creation date.  
        /// </summary>
        public DateTime CreatedAt {
            get {
                return this.createdAt;
            }
            set {
                if ( this.createdAt != value )
                    modifiedDBFields.Add( "created_at" );
                this.createdAt = value;
            }
        }

        /// <summary>
        /// The Person's creation field.
        /// </summary>
        protected DateTime updatedAt;

        /// <summary>
        /// The person's creation date.  
        /// </summary>
        public DateTime UpdatedAt {
            get {
                return this.updatedAt;
            }
            set {
                if ( this.updatedAt != value )
                    modifiedDBFields.Add( "updated_at" );
                this.updatedAt = value;
            }
        }

        /// <summary>
        /// The persons full name
        /// </summary>
        public string FullName {
            get {
                if ( this.middleName == null || this.middleName.Trim( ) == "" )
                    return string.Format( "{0} {1} {2}", name, patronymic, matronymic );
                else
                    return string.Format( "{0} {3} {1} {2}", name, patronymic, matronymic, middleName );
            }
        }

        /// <summary>
        /// The persons full name starting by last name.
        /// </summary>
        public string FullNameByLastName {
            get {
                if ( this.middleName == null || this.middleName.Trim( ) == "" )
                    return string.Format( "{1} {2} {0}", name, patronymic, matronymic );
                else
                    return string.Format( "{1} {2} {0} {3}", name, patronymic, matronymic, middleName );
            }
        }

        /// <summary>
        /// The Person's picture file.
        /// </summary>
        protected string pictureFile;

        /// <summary>
        /// The person's creation date.  
        /// </summary>
        public string PictureFile {
            get {
                return this.pictureFile;
            }
            set {
                if ( this.pictureFile != value )
                    modifiedDBFields.Add( "picture_file" );
                this.pictureFile = value;
            }
        }

        /// <summary>
        /// The person's contact info.
        /// </summary>
        public List<ContactField> ContactInfo {
            get {
                List<ContactField> cfl = new List<ContactField>( );
                Database db = DatabaseFactory.CreateDatabase( );
                DbCommand dbCommand;
                string sqlCommand = "SELECT cf.id, cf.name, cf.ordering FROM contact_fields AS cf, contact_info AS ci"
                    + " WHERE cf.id = ci.contact_field_id"
                    + " AND ci.person_id = @id"
                    + " ORDER BY ordering";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    while ( rdr.Read( ) ) {
                        cfl.Add( new ContactField( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( int ) rdr [ "ordering" ] ) );
                    }
                    rdr.Close( );
                }
                return cfl;
            }
        }

        /// <summary>
        /// Checks if the current instance is valid to be stored in the database.
        /// </summary>
        public override bool IsValid {
            get {
                return !( this.name == null ) && ( this.name.Trim( ) != "" )
                    && !( this.patronymic == null ) && ( this.patronymic.Trim( ) != "" )
                    && !( this.matronymic == null ) && ( this.matronymic.Trim( ) != "" )
                    && !( dateOfBirth == null );
            }
        }
        #endregion

        #region Constructors
        public Person ( ) {
            this.id = -1;
            this.name = "";
            this.middleName = "";
            this.patronymic = "";
            this.matronymic = "";
            this.dateOfBirth = DateTime.Now;
            this.createdAt = DateTime.Now;
            this.updatedAt = DateTime.Now;
            this.pictureFile = "";
            this.isNew = true;
        }

        public Person ( int id, string name, string middle_name, string patronymic, string matronymic, DateTime dateOfBirth, string pictureFile ) {
            this.id = id;
            this.name = name;
            this.middleName = middle_name;
            this.patronymic = patronymic;
            this.matronymic = matronymic;
            this.dateOfBirth = dateOfBirth;
            this.createdAt = DateTime.Now;
            this.updatedAt = DateTime.Now;
            this.pictureFile = pictureFile;
            this.isNew = ( id == -1 );
        }


        public Person ( int id, string name, string middle_name, string patronymic, string matronymic, DateTime dateOfBirth )
            : this( id, name, middle_name, patronymic, matronymic, dateOfBirth, string.Empty ) {
        }

        public Person ( string name, string middle_name, string patronymic, string matronymic, DateTime dateOfBirth )
            : this( -1, name, middle_name, patronymic, matronymic, dateOfBirth, string.Empty ) {
        }

        public Person ( string name, string middle_name, string patronymic, string matronymic, DateTime dateOfBirth, string pictureFile )
            : this( -1, name, middle_name, patronymic, matronymic, dateOfBirth, pictureFile ) {
        }
        #endregion

        #region Methods

        #region System.Object Methods
        /// <summary>
        /// Checks if the object is equal to the current instance.
        /// </summary>
        /// <param name="obj">The object to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public override bool Equals ( object obj ) {
            if ( obj == null ) {
                return false;
            }
            Person p = obj as Person;
            if ( ( object ) p == null ) {
                return false;
            }
            return ( this.name == p.name )
                && ( this.middleName == p.middleName )
                && ( this.patronymic == p.patronymic )
                && ( this.matronymic == p.matronymic )
                && ( this.dateOfBirth == p.dateOfBirth );
        }

        /// <summary>
        /// Checks if the Person is equal to the current instance.
        /// </summary>
        /// <param name="a">The Person to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public bool Equals ( Person p ) {
            if ( ( object ) p == null ) {
                return false;
            }
            return ( this.name == p.name )
                && ( this.middleName == p.middleName )
                && ( this.patronymic == p.patronymic )
                && ( this.matronymic == p.matronymic )
                && ( this.dateOfBirth == p.dateOfBirth );
        }

        public override int GetHashCode ( ) {
            return id.GetHashCode( )
                ^ this.name.GetHashCode( )
                ^ this.middleName.GetHashCode( )
                ^ this.patronymic.GetHashCode( )
                ^ this.matronymic.GetHashCode( )
                ^ this.dateOfBirth.GetHashCode( )
                ^ this.createdAt.GetHashCode( )
                ^ this.updatedAt.GetHashCode( )
                ^ this.pictureFile.GetHashCode( );
        }

        /// <summary>
        /// String representation of the current instance.
        /// </summary>
        /// <returns>A string representation of the current instance.</returns>
        public override string ToString ( ) {
            StringBuilder bldr = new StringBuilder( );
            object [ ] objArr = new object [ ] { this.name, this.middleName, this.patronymic, this.matronymic, this.dateOfBirth };
            bldr.AppendFormat( "{0} {1} {2} {3} ({4:dd/MM/YYYY})", objArr );
            return bldr.ToString( );
        }
        #endregion

        /// <summary>
        /// Saves the current instance to the database.
        /// </summary>
        /// <returns><code>True</code> if the object was saved to the database, <code>false</code> otherwise.</returns>
        public override bool Save ( ) {
            if ( !this.IsValid )
                return false;
            bool saved = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;
            try {
                sqlCommand = "SELECT count(*) = 0 as exists FROM persons WHERE id = @id";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                isNew = ( bool ) db.ExecuteScalar( dbCommand );
                if ( isNew ) {
                    sqlCommand = "SELECT nextval('persons_id_seq')";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    this.id = ( int ) db.ExecuteScalar( dbCommand );

                    sqlCommand = "INSERT INTO persons ( id, name, middle_name, patronymic, matronymic, dob, picture_file )"
                       + " VALUES ( @id, @name, @middle_name, @patronymic, @matronymic, @dob, @picture_file )";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@middle_name", DbType.String, this.middleName );
                    db.AddInParameter( dbCommand, "@patronymic", DbType.String, this.patronymic );
                    db.AddInParameter( dbCommand, "@matronymic", DbType.String, this.matronymic );
                    db.AddInParameter( dbCommand, "@dob", DbType.DateTime, this.dateOfBirth );
                    db.AddInParameter( dbCommand, "@picture_file", DbType.String, this.pictureFile );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
                else if ( this.modifiedDBFields.Count > 0 ) {
                    sqlCommand = "UPDATE persons SET name = @name, middle_name = @middle_name, patronymic = @patronymic,"
                        + "  matronymic = @matronymic, dob = @dob, picture_file = @picture_file"
                        + " WHERE id = @id";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@middle_name", DbType.String, this.middleName );
                    db.AddInParameter( dbCommand, "@patronymic", DbType.String, this.patronymic );
                    db.AddInParameter( dbCommand, "@matronymic", DbType.String, this.matronymic );
                    db.AddInParameter( dbCommand, "@dob", DbType.DateTime, this.dateOfBirth );
                    db.AddInParameter( dbCommand, "@picture_file", DbType.String, this.pictureFile );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                    //Remove udpated dbfields
                    foreach ( string dbField in this.DBFieldNames ) {
                        modifiedDBFields.Remove( dbField );
                    }
                }
                else {
                    //No update or insert needed
                    saved = true;
                }
            }
            catch ( Exception e ) {
                saved = false;
                this.errorMessages.Add( "There was an error saving the Person: " + e.Message );
            }
            return saved;
        }

        /// <summary>
        /// Deletes the current object from the database.
        /// </summary>
        /// <returns><code>True</code> if the object was deleted from the database, <code>false</code> otherwise.</returns>
        public override bool Delete ( ) {
            bool deleted = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;
            try {
                sqlCommand = "DELETE FROM persons WHERE id = @id";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                deleted = db.ExecuteNonQuery( dbCommand ) >= 0;
            }
            catch ( Exception e ) {
                deleted = false;
                this.errorMessages.Add( "There was an error saving the Person: " + e.Message );
            }
            return deleted;
        }


        #region Load Methods
        /// <summary>
        /// Loads the Person with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Persons' id.</param>
        /// <returns>A Person object with it's information or a null Person object if the <code>id</code> was not present in the database.</returns>
        public static Person Load ( int id ) {
            Person p = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT name, middle_name, patronymic, matronymic, dob, picture_file FROM persons WHERE id = @id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        p = new Person( id, ( string ) rdr [ "name" ], rdr [ "middle_name" ] as string, ( string ) rdr [ "patronymic" ],
                            ( string ) rdr [ "matronymic" ], ( DateTime ) rdr [ "dob" ], ( string ) rdr [ "picture_file" ] );
                        p.modifiedDBFields.Clear( );
                        p.ClearMessages( );
                        p.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                p.errorMessages.Add( string.Format( "There was an error loading the Person with ID {0}: {1}", id.ToString( ), e.Message ) );
            }
            return p;
        }

        /// <summary>
        /// Loads all of the Persons in the database to a list.
        /// </summary>
        /// <returns>A List containing all of the Persons on the database.</returns>
        public static List<Person> LoadAll ( ) {
            List<Person> pl = new List<Person>( );
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT id, name, middle_name, patronymic, matronymic, dob, picture_file FROM persons";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                while ( rdr.Read( ) ) {
                    pl.Add( new Person( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], rdr [ "middle_name" ] as string,
                            ( string ) rdr [ "patronymic" ], ( string ) rdr [ "matronymic" ],
                            ( DateTime ) rdr [ "dob" ], ( string ) rdr [ "picture_file" ] ) );
                }
                rdr.Close( );
            }
            return pl;
        }

        /// <summary>
        /// Checks the database for existence of the Person with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Persons' id.</param>
        /// <returns><code>True</code> if the person with the given <code>id</code>exists, <code>false</code> otherwise.</returns>
        public static bool Exists ( int id ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM persons WHERE id = @id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Checks the database for the given person's full name.
        /// </summary>
        /// <param name="name">Name</param>
        /// <param name="middle_name">Optional middle name</param>
        /// <param name="patronymic">Patronymic</param>
        /// <param name="matronymic">Matronymic</param>
        /// <param name="dateOfBirth">Date of Birth</param>
        /// <returns></returns>
        public static bool ExistsByName ( string name, string middle_name, string patronymic, string matronymic ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            string sqlCommand;
            DbCommand dbCommand;
            if ( middle_name.Trim( ) == "" ) {
                sqlCommand = "SELECT count(*) > 0 as exists FROM persons WHERE name ILIKE @name"
                     + " AND patronymic ILIKE @patronymic AND matronymic ILIKE @matronymic AND dob = @dob";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@patronymic", DbType.String, patronymic );
            }
            else {
                sqlCommand = "SELECT count(*) > 0 as exists FROM persons WHERE name ILIKE @name AND middle_name ILIKE @middle_name"
                     + " AND patronymic ILIKE @patronymic AND matronymic ILIKE @matronymic";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
            }
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            db.AddInParameter( dbCommand, "@patronymic", DbType.String, patronymic );
            db.AddInParameter( dbCommand, "@matronymic", DbType.String, matronymic );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }
        #endregion

        #endregion
    }
}
